// Licensed to the Apache Software Foundation (ASF) under one
// or more contributor license agreements.  See the NOTICE file
// distributed with this work for additional information
// regarding copyright ownership.  The ASF licenses this file
// to you under the Apache License, Version 2.0 (the
// "License"); you may not use this file except in compliance
// with the License.  You may obtain a copy of the License at
//
//   http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing,
// software distributed under the License is distributed on an
// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
// KIND, either express or implied.  See the License for the
// specific language governing permissions and limitations
// under the License.
suite("q78_spill") {
  sql """
    set enable_force_spill=true;
  """
  sql """
    set spill_min_revocable_mem=1;
  """
  sql """
    use regression_test_tpcds_sf1_unique_p1;
  """
/*
  qt_q78 """
-- at current, run q78 will get wrong result, still under fixing
WITH
  ws AS (
   SELECT
     d_year ws_sold_year
   , ws_item_sk
   , ws_bill_customer_sk ws_customer_sk
   , sum(ws_quantity) ws_qty
   , sum(ws_wholesale_cost) ws_wc
   , sum(ws_sales_price) ws_sp
   FROM
     web_sales
   LEFT JOIN web_returns ON (wr_order_number = ws_order_number)
      AND (ws_item_sk = wr_item_sk)
   INNER JOIN date_dim ON (ws_sold_date_sk = d_date_sk)
   WHERE (wr_order_number IS NULL)
   GROUP BY d_year, ws_item_sk, ws_bill_customer_sk
)
, cs AS (
   SELECT
     d_year cs_sold_year
   , cs_item_sk
   , cs_bill_customer_sk cs_customer_sk
   , sum(cs_quantity) cs_qty
   , sum(cs_wholesale_cost) cs_wc
   , sum(cs_sales_price) cs_sp
   FROM
     catalog_sales
   LEFT JOIN catalog_returns ON (cr_order_number = cs_order_number)
      AND (cs_item_sk = cr_item_sk)
   INNER JOIN date_dim ON (cs_sold_date_sk = d_date_sk)
   WHERE (cr_order_number IS NULL)
   GROUP BY d_year, cs_item_sk, cs_bill_customer_sk
)
, ss AS (
   SELECT
     d_year ss_sold_year
   , ss_item_sk
   , ss_customer_sk
   , sum(ss_quantity) ss_qty
   , sum(ss_wholesale_cost) ss_wc
   , sum(ss_sales_price) ss_sp
   FROM
     store_sales
   LEFT JOIN store_returns ON (sr_ticket_number = ss_ticket_number)
      AND (ss_item_sk = sr_item_sk)
   INNER JOIN date_dim ON (ss_sold_date_sk = d_date_sk)
   WHERE (sr_ticket_number IS NULL)
   GROUP BY d_year, ss_item_sk, ss_customer_sk
)
SELECT
  ss_sold_year
, ss_item_sk
, ss_customer_sk
, round((CAST(ss_qty AS DECIMAL(10,2)) / COALESCE((ws_qty + cs_qty), 1)), 2) ratio
, ss_qty store_qty
, ss_wc store_wholesale_cost
, ss_sp store_sales_price
, (COALESCE(ws_qty, 0) + COALESCE(cs_qty, 0)) other_chan_qty
, (COALESCE(ws_wc, 0) + COALESCE(cs_wc, 0)) other_chan_wholesale_cost
, (COALESCE(ws_sp, 0) + COALESCE(cs_sp, 0)) other_chan_sales_price
FROM
  ss
LEFT JOIN ws ON (ws_sold_year = ss_sold_year)
   AND (ws_item_sk = ss_item_sk)
   AND (ws_customer_sk = ss_customer_sk)
LEFT JOIN cs ON (cs_sold_year = ss_sold_year)
   AND (cs_item_sk = cs_item_sk)
   AND (cs_customer_sk = ss_customer_sk)
WHERE (COALESCE(ws_qty, 0) > 0)
   AND (COALESCE(cs_qty, 0) > 0)
   AND (ss_sold_year = 2000)
ORDER BY ss_sold_year ASC, ss_item_sk ASC, ss_customer_sk ASC, ss_qty DESC, ss_wc DESC, ss_sp DESC, other_chan_qty ASC, other_chan_wholesale_cost ASC, other_chan_sales_price ASC, round((CAST(ss_qty AS DECIMAL(10,2)) / COALESCE((ws_qty + cs_qty), 1)), 2) ASC
LIMIT 100
"""
*/
}
